package org.check.SQL;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import org.check.loadProperties.LoadProperty;

public class SqlLoader {
	
	public static String fileBaseName = "Select.sql";
	public static String startDateRegex = "@startDate";
	public static String endDateRegex = "@endDate";
	public static String selectRegex = "@select@";
	public static String filterRegex = "@filter";
	
	public static String historyRegex = "@history";
	public static String tableName = "activity_accumulator";
	
	public static String userRegex = "@USER_NAME";
	
	public static String connectionName = "connection.properties";
	
	public static void main(String[] args) {
		
		String test = loadByVersionType("SP16","oracle","test","20060101", "20161229","%");
		System.out.println(test);
	}
	
	public static String getUser(String type){
		Properties prop = LoadProperty.loadConnProp(type);
		String user_name = prop.getProperty("DBUSER");
		return user_name;
	}
	
	public static String loadByVersionType(String versionId, String type, String selectStr, String startDate, String endDate, String filter){
		String sqlStr = "";
		String filename = "/config/"+versionId+type+fileBaseName;

        StringBuilder sb = new StringBuilder(); 
        
		InputStreamReader in = null;
		try {
			File f = new File(System.getProperty("user.dir")+filename);
			FileInputStream  ins = new FileInputStream (f);
			in = new InputStreamReader(ins,"UTF-8");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
        try {   
        	BufferedReader reader = new BufferedReader(in);         
            String line = null;     
            while ((line = reader.readLine()) != null) {   
                sb.append(line + "\n");     
            }  
            sqlStr = sb.toString();
        } catch (IOException e) {   
            e.printStackTrace();   
        }  
        	
        sqlStr = sqlStr.replaceAll(selectRegex, selectStr);
        sqlStr = sqlStr.replaceAll(startDateRegex, startDate);
        sqlStr = sqlStr.replaceAll(endDateRegex, endDate);	
        String idFilter = "where t9.course_id like '"+filter+"'";
        sqlStr = sqlStr.replaceAll(filterRegex, idFilter);	
        sqlStr = sqlStr.replaceAll(userRegex, getUser(type));	
        
        /*SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        
        Date dateStart = null;
        Date dateEnd = null;
		try {
			dateStart = sdf.parse(startDate);
			dateEnd = sdf.parse(endDate);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		long  between = dateEnd.getTime() - dateStart.getTime();
		if(between > (180* 24* 3600000)){
			
			String tmp = ", "+getUser()+tableName;
		}*/
		return sqlStr;
	}
}
